package com.hebeu.xwl.dao.hivedao;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.annotation.InterceptorIgnore;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableLogic;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.hebeu.xwl.pojo.Article;
import org.apache.ibatis.annotations.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.repository.query.Param;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;


@Mapper
@DS("hive")
@InterceptorIgnore(dynamicTableName = "false")
public interface ArticleDao extends BaseMapper<Article> {


    @Update({
                "CREATE TABLE ${index} (\n" +
                "  `articleId` int ,\n" +
                "  `author` string ,\n" +
                "  `pubdate` string ,\n" +
                "  `pubtime` string ,\n" +
                "  `title` string ,\n" +
                "  `keywords` string ,\n" +
               // "  `content` string ,\n" +
                "  `href` string ,\n" +
                "  `likes` int ,\n" +
                "  `commentary` int ,\n" +
                "  `views` int \n" +
                ") row format delimited fields terminated by '\t';"
    })
    public void hiveCreatTable(@Param("index")String index);

    @Select({
        "select word,count(1) as `count` from " +
                "( select explode(split(lower(keywords), ',')) as word " +
                "from ${index} ) w " +
                "group by word " +
                "order by `count` desc;"
    })
    public List<Map<String,Object>> keywordsCount(@Param("index")String index);

    @Select({
            "select word,count(1) as `count` from " +
                    "( select explode(split(lower(keywords), ',')) as word " +
                    "from ${index} " +
                    "where author = '${author}') w " +
                    "group by word " +
                    "order by `count` desc;"
    })
    public List<Map<String,Object>> keywordsCountByAuthor(@Param("index")String index,
                                                     @Param("author")String author);


    @Select({
            "select author,sum(likes) as `count` " +
                    "from ${index} " +
                    "group by author " +
                    "order by `count` desc;"
    })
    public List<Map<String,Object>> sumLikesByAuthor(@Param("index") String index);

    @Select({
            "select author,sum(views) as `count` " +
                    "from ${index} " +
                    "group by author " +
                    "order by `count` desc;"
    })
    public List<Map<String,Object>> sumViewsByAuthor(@Param("index") String index);

    @Select({
            "select author,sum(commentary) as `count` " +
                    "from ${index} " +
                    "group by author " +
                    "order by `count` desc;"
    })
    public List<Map<String,Object>> sumCommentaryByAuthor(@Param("index") String index);


    @Update({
            "CREATE TABLE ${index_new} AS SELECT * FROM ${index_old};"
    })
    @InterceptorIgnore(dynamicTableName = "true")
    public void clone(@Param("index_new") String index_new
            ,@Param("index_old") String index_old);


    @Update({
            "create table ${index_new} as " +
                    "select * from ${index01} union select * from ${index02};"
    })
    @InterceptorIgnore(dynamicTableName = "true")
    public void merge(@Param("index_new") String index_new
                    ,@Param("index01") String index01
                    ,@Param("index02") String index02);


    @Select({
            "select pubdate,count(1) as `count` " +
                    "from ${index} " +
                    "group by pubdate " +
                    "order by pubdate desc;"
    })
    public List<Map<String,Object>> countByPubdate(@Param("index") String index);

    @Select({
            "select pubdate,count(1) as `count` " +
                    "from ${index} " +
                    "where author = '${author}' " +
                    "group by pubdate " +
                    "order by pubdate desc;"
    })
    public List<Map<String,Object>> countByPubdateAndAuthor(@Param("index") String index,
                                                       @Param("author") String author);

    @Update({
            "drop table ${index} ;"
    })
    public void dropTable(@Param("index") String index);

    @Insert({
            "insert overwrite ${index} values(${articleId},'${author}','${pubdate}','${pubtime}'," +
                    "'${title}','${keywords}','${href}',${likes},${commentary},${views});"
    })
    public void overwrite(@Param("index") String index,
                          @Param("articleId") int articleId,
                          @Param("author") String author,
                          @Param("pubdate") String pubdate,
                          @Param("pubtime") String pubtime,
                          @Param("title") String title,
                          @Param("keywords") String keywords,
                          @Param("href") String href,
                          @Param("likes") int likes,
                          @Param("commentary") int commentary,
                          @Param("views") int views);
}
